<?php

/**
 * Teacher filter form.
 *
 * @package    sf_sandbox
 * @subpackage filter
 * @author     Your name here
 * @version    SVN: $Id: sfDoctrineFormFilterTemplate.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class TeacherFormFilter extends BaseTeacherFormFilter {

    public function configure() {
        unset($this['intg_identity2'], $this['sub_id'], $this['nat_id']
                , $this['q_id'], $this['university'], $this['special_id'], $this['pre_school']
                , $this['work_date']
                , $this['job_id']
                , $this['birth_date']
                , $this['level']
                , $this['degree']
                , $this['emp_status']
                , $this['mobile']
                , $this['phone']
                , $this['q_date']
                , $this['created_by_user_id']
                , $this['updated_by_user_id']
                , $this['program_list']
        );

        /* $this->widgetSchema['intg_identity'] = new sfWidgetFormFilterInput();
          $this->ValidatorSchema['intg_identity'] = new sfValidatorInteger(array('required' => false));
         */
        $query = Doctrine::getTable('School')->createQuery('t');
        $query->addOrderBy('name');
        $this->widgetSchema['school_intg_id'] = new sfWidgetFormDoctrineChoice(array('model' => $this->getRelatedModelName('School'), 'add_empty' => true, 'query' => $query, 'key_method' => 'getIntgId'));
        $this->validatorSchema['school_intg_id'] = new sfValidatorDoctrineChoice(array('required' => false, 'model' => $this->getRelatedModelName('School'), 'column' => 'id', 'column' => 'intg_id'));

        $hange_status = array('' => ' ');
        foreach (TeacherTable::$hang_status_arr as $k_val => $k_word) {
            $hange_status[$k_val] = $k_word;
        }
        unset($hange_status[TeacherTable::HANG_STATUS_CANCELED]);

        $this->widgetSchema['hange_status'] = new sfWidgetFormChoice(array(
                    'choices' => $hange_status
                ));
        $this->validatorSchema['hange_status'] = new sfValidatorChoice(array('choices' => array_keys($hange_status), 'required' => false));
    }

    public function getFields() {
        return array(
            'intg_identity' => 'Number',
            'intg_identity2' => 'Number',
            'school_intg_id' => 'ForeignKey',
            'sub_id' => 'Number',
            'f_name' => 'Text',
            's_name' => 'Text',
            't_name' => 'Text',
            'l_name' => 'Text',
            'nat_id' => 'Number',
            'q_id' => 'Number',
            'q_date' => 'Date',
            'university' => 'Number',
            'special_id' => 'Number',
            'pre_school' => 'ForeignKey',
            'work_date' => 'Date',
            'job_id' => 'Number',
            'birth_date' => 'Date',
            'level' => 'Number',
            'degree' => 'Number',
            'emp_status' => 'Number',
            'mobile' => 'Number',
            'phone' => 'Number',
            'is_manager' => 'Boolean',
            'is_trainer' => 'Boolean',
            'is_hanged' => 'Boolean',
            'hange_status' => 'Enum',
            'created_by_user_id' => 'ForeignKey',
            'updated_by_user_id' => 'ForeignKey',
            'program_list' => 'ManyKey',
        );
    }

    public function doBuildQueryForReport($values) {
        $q = parent::doBuildQuery($values);

        if ($values['location_of_holding'] != '' || (isset($values['code_sector_list']) && $values['code_sector_list'] != '') || (isset($values['code_target_list']) && $values['code_target_list'] != '') || $values['program_code']['text'] != '' || $values['trainer_id'] != '' || $values['is_mandatory'] != '') {
            $q->innerJoin('r.LinkTeacherProgram link');
            $q->innerJoin('link.Program p');
        }

        if ($values['program_code']['text'] != '') {
            $q->addWhere('p.program_code = ?', $values['program_code']['text']);
        }

        if ($values['location_of_holding'] != '') {
            $q->addWhere('p.location_of_holding = ?', $values['location_of_holding']);
        }

        if ($values['trainer_id'] != '') {
            $q->addWhere('p.trainer_id = ?', $values['trainer_id']);
        }

        if ($values['is_mandatory'] != '') {
            $q->addWhere('p.is_mandatory = ?', $values['is_mandatory']);
        }

        if (isset($values['code_target_list']) && $values['code_target_list'] != '') {
            $q->innerJoin('p.CodeTarget ct');
            $q->whereIn('ct.id', $values['code_target_list']);
        }

        if (isset($values['code_sector_list']) && $values['code_sector_list'] != '') {
            $q->innerJoin('p.CodeSector cs');
            $q->whereIn('cs.intg_id', $values['code_sector_list']);
        }

        if ($values['intg_identity'] != '') {
            $q->addWhere('r.intg_identity = ?', $values['intg_identity']);
        }

        $q->select('r.intg_identity, CONCAT(r.f_name," ",r.s_name," ",r.t_name," ",r.l_name) as name');
        $q->addSelect('
(SELECT 
    COUNT(link2.program_id) 
  FROM
    LinkTeacherProgram link2
  WHERE r.intg_identity = link2.teacher_id 
    AND link2.assign_status = "2") as count_of_assigned_programs');

        $q->orderBy('r.f_name ASC');
        return $q;
    }

}
